/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package jlxy.ise.eroad.service;

import java.util.List;
import jlxy.ise.eroad.util.SqlOperator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author shao
 */
@Service
@Transactional
public class FreightService {
    @Autowired
    private SqlOperator sql;

//    //添加管理员
//    public Sret addAdmin(String username, String passwd, String power) {
//        String stmt = "insert into admin(username, passwd, power) values (?,?,?)";
//        String id = this.sql.insert(stmt, username, passwd, power)[0];
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(id);
//        return sr;
//    }
//
//    //列出所有管理员
//    public Sret listAdmin() {
//        String stmt = "select * from admin";
//        List list = this.sql.query(stmt);
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(list);
//        return sr;
//    }
//    
//    //搜索管理员
//    public Sret searchAdmin(String username) {
//        //System.out.println(username);
//        String stmt = "select * from admin where username like '%"+username+"%'";
//        List search = this.sql.query(stmt);
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(search);
//        return sr;
//    }
//
//    //修改管理员信息
//    public Sret alterAdmin(String username, String id) {
//        String stmt = "update admin set username = ? where id = ?";
//        this.sql.update(stmt, username, id);
//        Sret sr = new Sret();
//        sr.setOk();
//        return sr;
//    }
//    
//    //删除管理员
//    public Sret removeAdmin(String id) {
//        String stmt = "delete from admin where id = ?";
//        this.sql.delete(stmt, id);
//        Sret sr = new Sret();
//        sr.setOk();
//        return sr;
//    }

    //列出所有司机
    public Sret listDriver() {
        String stmt = "select * from driver";
        List list = this.sql.query(stmt);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //搜索手机号，查询司机id
    public Sret searchPhone(String telephone) {
//        System.out.println(telephone);
        String stmt = "select id from driver where telephone = ?";
        List search = this.sql.query(stmt, telephone);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(search);
        return sr;
    }
    
    //注册页面，添加司机
    public Sret addDriver(String telephone, String passwd, String registertime) {
        String stmt = "insert into driver(telephone, passwd, registertime) values (?,?,?)";
        String id = this.sql.insert(stmt, telephone, passwd, registertime)[0];
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(id);
        return sr;
    }
    
    //司机信息页面，添加司机信息1
    public Sret addDriver1(String name, String drivingnumber, String emergencyperson, String emergencynumber, String id) {
        String stmt = "update driver set name = ?,drivingnumber = ?,emergencyperson = ?,emergencynumber = ? where id = ?";
        this.sql.update(stmt, name, drivingnumber, emergencyperson, emergencynumber, id);
        Sret sr = new Sret();
        sr.setOk();
        return sr;
    }
    
    //车辆信息页面，添加司机信息2
    public Sret addDriver2(String username, String operationnumber, String vin, String enginenumber, String brandmodel, String type, String length, String width, String height, String capacity, String isowner, String description, String id) {
        String stmt = "update driver set username = ?,operationnumber = ?,vin = ?,enginenumber = ?,brandmodel = ?,type = ?,length = ?,width = ?,height = ?,capacity = ?,isowner = ?,description = ? where id = ?";
        this.sql.update(stmt, username, operationnumber, vin, enginenumber, brandmodel, type, length, width, height, capacity, isowner, description, id);
        Sret sr = new Sret();
        sr.setOk();
        return sr;
    }
    
    //照片信息页面，添加司机信息3
    public Sret addDriver3(String drivinglicense, String operationlicense, String travellicense, String vehiclephoto, String id) {
        String stmt = "update driver set drivinglicense = ?,operationlicense = ?,travellicense = ?,vehiclephoto = ? where id = ?";
        this.sql.update(stmt, drivinglicense, operationlicense, travellicense, vehiclephoto, id);
        Sret sr = new Sret();
        sr.setOk();
        return sr;
    }
    
    //登录验证
    public Sret loginVerify(String telephone, String passwd) {
        String stmt = "select * from driver where telephone = ? and passwd = ?";
        List search = this.sql.query(stmt,telephone, passwd);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(search);
        return sr;
    }
    
    //登录时修改登录时间和账号状态
    public Sret loginAlter(String lastlogintime, String status, String id) {
        String stmt = "update driver set lastlogintime = ?,status = ? where id = ?";
        this.sql.update(stmt, lastlogintime, status, id);
        Sret sr = new Sret();
        sr.setOk();
        return sr;
    }
    
    //注销时修改账号状态
    public Sret exitAlter(String status, String id) {
        String stmt = "update driver set status = ? where id = ?";
        this.sql.update(stmt, status, id);
        Sret sr = new Sret();
        sr.setOk();
        return sr;
    }
    
    //忘记密码验证
    public Sret forgetVerify(String telephone, String drivingnumber) {
        String stmt = "select * from driver where telephone = ? and drivingnumber = ?";
        List search = this.sql.query(stmt,telephone, drivingnumber);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(search);
        return sr;
    }
    
    //重置密码
    public Sret resetPasswd(String passwd, String id) {
        String stmt = "update driver set passwd = ? where id = ?";
        this.sql.update(stmt, passwd, id);
        Sret sr = new Sret();
        sr.setOk();
        return sr;
    }
    
    //查看个人资料
    public Sret listInfo(String id) {
        String stmt = "select * from driver where id = ?";
        List search = this.sql.query(stmt,id);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(search);
        return sr;
    }
    
     //修改个人资料
    public Sret alterInfo(String name, String drivingnumber, String emergencyperson, String emergencynumber, String username, String operationnumber, String vin, String enginenumber, String brandmodel, String type, String length, String width, String height, String capacity, String isowner, String description, String drivinglicense, String operationlicense, String travellicense, String vehiclephoto, String id) {
        String stmt = "update driver set name = ?,drivingnumber = ?,emergencyperson = ?,emergencynumber = ?,username = ?,operationnumber = ?,vin = ?,enginenumber = ?,brandmodel = ?,type = ?,length = ?,width = ?,height = ?,capacity = ?,isowner = ?,description = ?,drivinglicense = ?,operationlicense = ?,travellicense = ?,vehiclephoto = ? where id = ?";
        this.sql.update(stmt, name, drivingnumber, emergencyperson, emergencynumber, username, operationnumber, vin, enginenumber, brandmodel, type, length, width, height, capacity, isowner, description, drivinglicense, operationlicense, travellicense, vehiclephoto, id);
        Sret sr = new Sret();
        sr.setOk();
        return sr;
    }
    
//    //搜索意见
//    public Sret searchFeedback(String uid) {
//        String stmt = "select * from feedback where uid = ?";
//        List search = this.sql.query(stmt, uid);
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(search);
//        return sr;
//    }
//    
//    //添加意见
//    public Sret addFeedback(String content, String time, String uid) {
//        String stmt = "insert into feedback(content, time, uid) values (?,?,?)";
//        String id = this.sql.insert(stmt, content, time, uid)[0];
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(id);
//        return sr;
//    }
    
    //修改车辆状态
    public Sret alterState(String state, String id) {
        String stmt = "update driver set state = ? where id = ?";
        this.sql.update(stmt, state, id);
        Sret sr = new Sret();
        sr.setOk();
        return sr;
    }
    
    //根据uid列出该司机所有感兴趣的城市
    public Sret listInterest(String uid) {
        String stmt = "select * from interest where uid = ?";
        List list = this.sql.query(stmt, uid);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //列出省名
    public Sret listProvince() {
        String stmt = "select * from province";
        List list = this.sql.query(stmt);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //根据id查询对应的省名
    public Sret queryPname(String id) {
        String stmt = "select name from province where id = ?";
        List list = this.sql.query(stmt, id);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //列出城市名
    public Sret listCity() {
        String stmt = "select * from city";
        List list = this.sql.query(stmt);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //根据pid列出与该省对应的所有城市
    public Sret searchCity(String pid) {
        String stmt = "select * from city where pid = ?";
        List list = this.sql.query(stmt, pid);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //根据id查询对应的城市名
    public Sret queryCname(String id) {
        String stmt = "select name from city where id = ?";
        List list = this.sql.query(stmt, id);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //添加感兴趣的城市
    public Sret addInterest(String pname, String cname, String uid) {
        String stmt = "insert into interest(pname, cname, uid) values (?,?,?)";
        String id = this.sql.insert(stmt, pname, cname, uid)[0];
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(id);
        return sr;
    }
    
    //根据cname和uid判断该城市是否已添加
    public Sret searchInterest(String cname, String uid) {
        String stmt = "select * from interest where cname = ? and uid = ?";
        List search = this.sql.query(stmt,cname, uid);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(search);
        return sr;
    }
    
    //删除感兴趣的城市
    public Sret removeInterest(String id) {
        String stmt = "delete from interest where id = ?";
        this.sql.delete(stmt, id);
        Sret sr = new Sret();
        sr.setOk();
        return sr;
    }
    
    //列出区名
    public Sret listDistrict() {
        String stmt = "select * from district";
        List list = this.sql.query(stmt);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //根据cid列出与该城市对应的所有区县
    public Sret searchDistrict(String cid) {
        String stmt = "select * from district where cid = ?";
        List list = this.sql.query(stmt, cid);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //根据id查询对应的区名
    public Sret queryDname(String id) {
        String stmt = "select name from district where id = ?";
        List list = this.sql.query(stmt, id);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //筛选发货省
    public Sret filterSaddress1(String sprovince, String flag) {
        String stmt = "select * from allorder where sprovince = ? and flag = ?";
        List list = this.sql.query(stmt, sprovince, flag);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //筛选发货省、市
    public Sret filterSaddress2(String sprovince, String scity, String flag) {
        String stmt = "select * from allorder where sprovince = ? and scity = ? and flag = ?";
        List list = this.sql.query(stmt, sprovince, scity, flag);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //筛选发货省、市、区
    public Sret filterSaddress3(String sprovince, String scity, String sdistrict, String flag) {
        String stmt = "select * from allorder where sprovince = ? and scity = ? and sdistrict = ? and flag = ?";
        List list = this.sql.query(stmt, sprovince, scity, sdistrict, flag);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //筛选收货省
    public Sret filterEaddress1(String eprovince, String flag) {
        String stmt = "select * from allorder where eprovince = ? and flag = ?";
        List list = this.sql.query(stmt, eprovince, flag);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //筛选收货省、市
    public Sret filterEaddress2(String eprovince, String ecity, String flag) {
        String stmt = "select * from allorder where eprovince = ? and ecity = ? and flag = ?";
        List list = this.sql.query(stmt, eprovince, ecity, flag);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //筛选收货省、市、区
    public Sret filterEaddress3(String eprovince, String ecity, String edistrict, String flag) {
        String stmt = "select * from allorder where eprovince = ? and ecity = ? and edistrict = ? and flag = ?";
        List list = this.sql.query(stmt, eprovince, ecity, edistrict, flag);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //列出竞价中的所有订单
    public Sret listPackage() {
        String stmt = "select * from allorder where flag = 2";
        List list = this.sql.query(stmt);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //根据id查询订单的详细信息
    public Sret searchPackage(String id) {
        String stmt = "select * from allorder where id = ?";
        List search = this.sql.query(stmt, id);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(search);
        return sr;
    }
    
    //添加竞价用户和报价
    public Sret addBidding(String uid, String price, String uname, String pid) {
        String stmt = "insert into bidding(uid, price, uname, pid) values (?,?,?,?)";
        String id = this.sql.insert(stmt, uid, price, uname, pid)[0];
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(id);
        return sr;
    }
    
    //取消报价
    public Sret removeBidding(String uid, String pid) {
        String stmt = "delete from bidding where uid = ? and pid = ?";
        this.sql.delete(stmt, uid, pid);
        Sret sr = new Sret();
        sr.setOk();
        return sr;
    }
    
    //判断用户是否参与该订单的竞价
    public Sret searchBidding(String uid, String pid) {
        String stmt = "select * from bidding where uid = ? and pid = ?";
        List search = this.sql.query(stmt, uid, pid);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(search);
        return sr;
    }
    
    //定时上报位置
    public Sret addLocation(String lon, String lat, String timestamp, String uid) {
        String stmt = "insert into location(lon, lat, timestamp, uid) values (?,?,?,?)";
        String id = this.sql.insert(stmt, lon, lat, timestamp, uid)[0];
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(id);
        return sr;
    }
    
//    //id降序列出所有公告
//    public Sret listNotice() {
//        String stmt = "select * from notice allorder by id desc";
//        List list = this.sql.query(stmt);
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(list);
//        return sr;
//    }
//    
//    //根据id查询对应的公告
//    public Sret searchNotice(String id) {
//        String stmt = "select * from notice where id = ?";
//        List search = this.sql.query(stmt, id);
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(search);
//        return sr;
//    }
    
    //根据uid查询所有用户订单及对应的评分、评价
    public Sret queryEvaluation(String uid) {
        String stmt = "select * from allorder where uid = ?";
        List search = this.sql.query(stmt, uid);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(search);
        return sr;
    }
    
    //列出用户进行中的订单
    public Sret listOngoing(String uid) {
        String stmt = "select * from allorder where uid = ? and flag = 1";
        List list = this.sql.query(stmt, uid);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //列出用户已完成的订单
    public Sret listCompleted(String uid) {
        String stmt = "select * from allorder where uid = ? and flag = 0";
        List list = this.sql.query(stmt, uid);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    //列出用户已取消的订单
    public Sret listCanceled(String uid) {
        String stmt = "select * from allorder where uid = ? and flag = 4";
        List list = this.sql.query(stmt, uid);
        Sret sr = new Sret();
        sr.setOk();
        sr.setData(list);
        return sr;
    }
    
    public Sret exception1() {
        throw new IllegalArgumentException("抛出异常1");
    }
	
//    //登录验证
//    public Sret LoginPass(String phone, String pwd) {
//        String stmt = "select * from db_user where phone = ? and pwd = ?";
//        List search = this.sql.query(stmt, phone, pwd);
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(search);
//        return sr;
//    }
//    
//    //添加用户
//    public Sret addUser(String nickname, String phone, String type, String pwd, String email) {
//        String stmt = "insert into db_user(nickname, phone, type, pwd, email) values (?,?,?,?,?)";
//        String id = this.sql.insert(stmt, nickname, phone, type, pwd, email)[0];
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(id);
//        return sr;
//    }
//    
//    //获取车辆状态
//    public Sret getStatus(String id) {
//        String stmt = "select status from db_request where id = ?";
//        List search = this.sql.query(stmt,id);
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(search);
//        return sr;
//    }
//    
//    //修改个人资料
//    public Sret alterInfo(String nickname, String email, String phone, String id) {
//        String stmt = "update db_user set nickname = ?,email=?,phone=? where id = ?";
//        this.sql.update(stmt, nickname, email, phone, id);
//        Sret sr = new Sret();
//        sr.setOk();
//        return sr;
//    }
//	    
//    //修改车辆状态
//    public Sret alterStatus(String status, String id) {
//        String stmt = "update db_user set status = ? where id = ?";
//        this.sql.update(stmt, status, id);
//        Sret sr = new Sret();
//        sr.setOk();
//        return sr;
//    }
//    
//    //获取订单
//    public Sret getOrder(String user_id, String priority) {
//        String stmt = "select * from db_request where user_id = ? and priority = ?";
//        List search = this.sql.query(stmt, user_id, priority);
//        Sret sr = new Sret();
//        sr.setOk();
//        sr.setData(search);
//        return sr;
//    }
}
